<?php
/**
 * Author: yixuan
 * Desc:数据库操作函数
 * require_path: 挂载文件路径  application/common.php:15
 * Time: 2016/5/18 17:31
 */

use think\Db;
use think\facade\log;
use think\facade\config;

/**
 * Desc:智能补全表前缀
 * User: yixuan
 * Time: 2016/5/8 19:48
 * @param $name
 * @return mixed|string
 */
if (!function_exists('tablename_in')) {
    function tablename_in($name)
    {
        $prefix = config('database.prefix');
        if (strpos($name, $prefix) === 0 || strpos($name, 'yx_') === 0) {
            return $name;
        } else {
            return $prefix . $name;
        }
    }
}
if (!function_exists('tablename')) {
    function tablename($name)
    {
        $prefix = config('database.prefix');
        if (strpos($name, $prefix) === 0 || strpos($name, 'yx_') === 0) {
            return " " . $name . " ";
        } else {
            return " " . $prefix . $name . " ";
        }
    }
}
/**
 * Desc:格式化where
 * User: yixuan
 * Time: 2016/5/8 19:47
 * @param $where
 * @return array|string
 */
if (!function_exists('db_where')) {
    function db_where($where = [])
    {
        if (!empty($where)) {
            $result = '';
            foreach ($where as $key => $value) {
                if (strpos($key, '<') !== false) {
                    $result .= where_str($result, $key, $value);
                    continue;
                } elseif (strpos($key, '>') !== false) {
                    $result .= where_str($result, $key, $value);
                    continue;
                } elseif (strpos($key, '<>') !== false) {
                    $result .= where_str($result, $key, $value);
                    continue;
                } elseif (strpos($key, '!=') !== false) {
                    $result .= where_str($result, $key, $value);
                    continue;
                } elseif (strpos($key, '<=') !== false) {
                    $result .= where_str($result, $key, $value);
                    continue;
                } else {
                    $result .= where_str($result, $key, $value, '=');
                    continue;
                }
            }
            return $result;
        } else {
            return [];
        }
    }
}
/**
 * Desc:组装条件语句
 * User: yixuan
 * Time: 2016/6/8 13:23
 * @param $result
 * @param $key
 * @param $value
 * @param $symbol
 * @return string
 */
if (!function_exists('where_str')) {
    function where_str($result, $key, $value, $symbol = '')
    {
        if (!is_numeric($value)) {
            $value = "'" . $value . "'";
        }
        if (!empty($symbol)) {
            if (empty($result)) {
                $result = $key . $symbol . $value;
            } else {
                $result = ' ' . 'AND  ' . $key . $symbol . $value;
            }
        } else {
            if (empty($result)) {
                $result = $key . $value;
            } else {
                $result = ' ' . 'AND  ' . $key . $value;
            }
        }
        return $result;
    }
}
/**
 * Desc:删除数据
 * User: yixuan
 * Time: 2016/5/8 19:47
 * @param $tablename_in
 * @param $where
 * @return int|string
 */
if (!function_exists('db_delete')) {
    function db_delete($tablename_in, $where = [])
    {
        $tablename_in = tablename_in($tablename_in);
        if (empty($where)) {
            $where = '1=1';
        } else {
            $where = db_where($where);
        }
        $result = DB::table($tablename_in)
            ->where($where)
            ->delete();
        return $result;
    }
}
/**
 * Desc:更新数据
 * User: yixuan
 * Time: 2016/5/8 19:47
 * @param $tablename_in
 * @param $data
 * @param $where
 * @return int|string
 */
if (!function_exists('db_update')) {
    function db_update($tablename_in, $data = [], $where = [])
    {
        foreach ($data as $key => $value) {
            if ($value === null) {  // || $value === ''
                unset($data[$key]);
            }
        }
        $tablename_in = tablename_in($tablename_in);
        $where = db_where($where);
        $result = DB::table($tablename_in)
            ->where($where)
            ->update($data);
        return $result;
    }
}
/**
 * Desc:写入数据
 * Time: 2016/5/8 19:45
 * @param $tablename_in
 * @param $data
 * @return int|string
 */
if (!function_exists('db_insert')) {
    function db_insert($tablename_in, $data = [])
    {
        foreach ($data as $key => $value) {
            if ($value === null) {
                unset($data[$key]);
            }
        }
        $tablename_in = tablename_in($tablename_in);
        $id = Db::table($tablename_in)->insertSetGetId($data);
        return $id;
    }
}
/**
 * Desc:查询
 * User: yixuan
 * Time: 2016/5/8 19:48
 * @param $sql
 * @param $where
 * @return mixed
 */
if (!function_exists('db_select')) {
    function db_select($sql, $where = [], $keyfield = '')
    {

        if (!empty($where)) {
            foreach ($where as $key => $value) {
                $sql = str_replace($key, "'" . $value . "'", $sql);
            }
        }
        $result = DB::query($sql);
        if (!empty($keyfield)) {
            $result_list = [];
            foreach ($result as $v) {
                $result_list[$v[$keyfield]] = $v;
            }
            return $result_list;
        }
        return $result;
    }
}
/***
 * Desc:查一条
 * User: yixuan
 * Time: 2016/5/8 19:59
 * @param $tablename_in
 * @param $where
 * @param $field
 * @return array|Closure|false|PDOStatement|string|\think\db\Query|\think\Model|null
 */
if (!function_exists('db_get')) {
    function db_get($tablename_in, $where = [], $field = '*')
    {
        $tablename_in = tablename_in($tablename_in);
        $where = db_where($where);
        $result = DB::table($tablename_in);
        if (!empty($field)) {
            $field = implode(',', $field);
            $result = $result->field($field);
        }
        $result = $result->where($where);
        $result = $result->find();
        return $result;
    }
}
/***
 * Desc:查一条
 * User: yixuan
 * Time: 2016/5/8 19:59
 * @param $tablename_in
 * @param $where
 * @param $field
 * @return array|Closure|false|PDOStatement|string|\think\db\Query|\think\Model|null
 */
if (!function_exists('db_find')) {
    function db_find($sql, $where = [])
    {
        if (!empty($where)) {
            foreach ($where as $key => $value) {
                $sql = str_replace($key, "'" . $value . "'", $sql);
            }
        }
        $result = DB::query($sql);
        return $result[0];
    }
}
/**
 * Desc:查
 * User: yixuan
 * Time: 2016/5/8 19:59
 * @param $tablename_in
 * @param $where
 * @param $field
 * @param $keyfield
 * @param $order
 * @param $limit
 * @return array|false|PDOStatement|string|\think\Collection
 */
if (!function_exists('db_getall')) {
    function db_getall($tablename_in, $where = [], $field = [], $keyfield = '', $order = [], $limit = [])
    {
        $tablename_in = tablename_in($tablename_in);
        $result = DB::table($tablename_in);
        if (!empty($where)) {
            $where = db_where($where);
            $result = $result->where($where);
        }
        if (!empty($field)) {
            $field = implode(',', $field);
            $result = $result->field($field);
        }
        if (!empty($order)) {
            $result = $result->order($order);
        }
        if (!empty($limit)) {
            $limit = implode(',', $limit);
            $result = $result->limit($limit);
        }
        $result = $result->select();
        if (!empty($keyfield)) {
            $result_list = [];
            foreach ($result as $v) {
                $result_list[$v[$keyfield]] = $v;
            }
            return $result_list;
        }
        return $result;
    }
}
if (!function_exists('db_query')) {
    function db_query($sql, $where = [])
    {
        if (!empty($where)) {
            foreach ($where as $key => $value) {
                $sql = str_replace($key, "'" . $value . "'", $sql);
            }
        }
        $sqlnew = strtolower($sql);
        if (strpos($sqlnew, 'select') === 0) {
            $result = DB::query($sql);
        } elseif (strpos($sqlnew, 'update') === 0) {
            $result = DB::execute($sql);
        } else {
            $result = db_run($sql);
        }
        return $result;
    }
}


if (!function_exists('db_run')) {
    function db_run($sql)
    {
        $database = \config()['database'];
        $mysqli = mysqli_connect($database['hostname'], $database['username'], $database['password'], $database['database'], $database['hostport']);
        // 检查连接是否成功
        if (!$mysqli) {
            die('连接到 MySQL 服务器失败: ' . mysqli_connect_error());
        }

        // 执行多条SQL语句
        if (!mysqli_multi_query($mysqli, $sql)) {
            echo('执行批量sql 语句时出错: ' . mysqli_error($mysqli));
        }

        // 处理每一条查询的结果和可能的错误
        do {
            if (mysqli_more_results($mysqli)) {
                mysqli_next_result($mysqli);
            }
            // 虽然这里不需要处理结果集，但检查并处理错误是必要的
            if ($error = mysqli_error($mysqli)) {
                echo '执行语句时出错: ' . $error;
            }
        } while (mysqli_more_results($mysqli));

        // 在所有语句执行完毕后关闭连接
        mysqli_close($mysqli);
    }
}
/**
 * Desc:检查表
 * User: yixuan
 * Time: 2016/5/18 16:10
 * @param $tablename_in
 * @return mixed
 */
if (!function_exists('db_tableexists')) {
    function db_tableexists($tablename_in)
    {
        $tablename_in = tablename_in($tablename_in);
        $sql = "show tables like '$tablename_in'";
        $result = DB::query($sql);
        return $result;
    }
}
/**
 * Desc:检查字段
 * User: yixuan
 * Time: 2016/5/18 16:10
 * @param $tablename_in
 * @param $field
 * @return mixed
 */
if (!function_exists('db_fieldexists')) {
    function db_fieldexists($tablename_in, $field = '')
    {
        $tablename_in = tablename_in($tablename_in);
        $sql = "show columns from $tablename_in like '$field'";
        $result = DB::query($sql);
        return $result;
    }
}
/**
 *  * Desc:检查索引
 */
if (!function_exists('db_indexexists')) {
    function db_indexexists($tablename_in, $field = '')
    {
        $tablename_in = tablename_in($tablename_in);
        $result = DB::query("SHOW INDEX FROM " . $tablename_in . " WHERE Column_name = '" . $field . "';");
        return $result;
    }
}
if (!function_exists('db_fetchcolumn')) {
    function db_fetchcolumn($sql, $where = [])
    {

        if (!empty($where)) {
            foreach ($where as $key => $value) {
                $sql = str_replace($key, "'" . $value . "'", $sql);
            }
        }
        $result = DB::query($sql);
        foreach ($result[0] as $v) {
            return $v;
        }

    }
}
if (!function_exists('db_insert_all')) {
    function db_insert_all($table, $data, $insert_txt = 'INSERT INTO ')
    {
        if (empty($table)) {
            return false;
        }
        if (!count($data)) {
            return false;
        }
        $keys = array_keys(current($data));
        $fields = implode(',', $keys);

        $sql = $insert_txt . tablename_in($table) . " (" . $fields . ") VALUES ";
        foreach ($data as $item) {
            $itemStr = "('" . str_replace(",", "','", implode(',', $item)) . "'),";;
            $sql .= $itemStr;
        }
        // 去除最后一个逗号，并且加上结束分号
        $sql = rtrim($sql, ',');
        $ret = DB::execute($sql);
        return $ret;
    }
}
if (!function_exists('db_updateall')) {
    function db_updateall($table, $data, $field = 'id', $where = [])
    {
        if (empty($table)) {
            return false;
        }
        if (!count($data)) {
            return false;
        }
        $update_sql = '';
        $keys = array_keys(current($data));

        foreach ($keys as $column) {
            $update_sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
            foreach ($data as $line) {
                $update_sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
            }
            $update_sql .= "END,";
        }

        if ($update_sql) {
            $updates = rtrim($update_sql, ',');
        } else {
            return false;
        }

        if ($where) {
            foreach ($where as $key => $value) {
                $where[] = sprintf("`%s` = '%s'", $key, $value);
            }
            $where = $where ? ' AND ' . implode(' AND ', $where) : '';
        } else {
            $where = '';
        }

        $fields_c = array_map(function ($v) use ($field) {
            return $v["$field"];
        }, $data);
        $fields = implode(',', array_map(function ($value) {
            return "'" . $value . "'";
        }, $fields_c));

        $sql = sprintf("UPDATE %s SET %s WHERE `%s` IN (%s) %s", tablename_in($table), $updates, $field, $fields, $where);
        $ret = DB::execute($sql);
        return $ret;
    }
}
if (!function_exists('db_getcolumn')) {
    /**
     * Desc:查询
     * User: yixuan
     * Time: 2016/5/8 19:48
     * @param $sql
     * @param $where
     * @return mixed
     */
    function db_getcolumn($tablename_in, $where = [], $field = '')
    {
        $res = db_get($tablename_in, $where, $field);
        if ($res && $field) {
            return $res[$field];
        }
        return $res;
    }
}